package com.pat.java.tools;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Date;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Excel2010Operation {
	// ignoreRows 表示读取某个表时，忽略的行数。
	public List<List<String[]>> getData(File file, String privilegeType, int ignoreRows) throws FileNotFoundException, IOException {

		List<List<String[]>> sheets = new ArrayList<List<String[]>>();// 一个list存储一个工作簿

		BufferedInputStream in = new BufferedInputStream(new FileInputStream(file));

		XSSFWorkbook workbook = new XSSFWorkbook(in);

		// start read a sheet
		sheets = getWorkBook(workbook, privilegeType, ignoreRows);
		// end read a sheet

		in.close();
		return sheets;

	}

	// start 获取excle 一个 工作簿(workBook)的记录
	public List<List<String[]>> getWorkBook(XSSFWorkbook workbook, String privilegeType, int ignoreRows) {

		// 一个list存储一个工作簿
		List<List<String[]>> sheets = new ArrayList<List<String[]>>();

		for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
			List<String[]> result = new ArrayList<String[]>();// 一个list存储一个sheet的所有行
			XSSFSheet sheet = workbook.getSheetAt(sheetIndex);
			String sheetName = sheet.getSheetName(); // 获取sheet的名字

			if ("ALL".equalsIgnoreCase(privilegeType)) {
			} else if (!sheetName.equalsIgnoreCase(privilegeType)) {
				continue;
			}
			// System.out.println(sheetName);

			// 获取表格，ignoreRows为忽略的行数；

			result = getExcelSheet(sheet, ignoreRows);
			sheets.add(result);
		}

		return sheets;
	}
	// end 获取excle 一个 工作簿(workBook)的记录
	// start 获取excle 一个表(sheet)的记录
	public List<String[]> getExcelSheet(XSSFSheet xssfSheet, int ignoreRows) {
		XSSFSheet sheet = xssfSheet;

		// 一个list存储一个workBook的所有行
		List<String[]> result = new ArrayList<String[]>();

		for (int rowIndex = ignoreRows; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
			XSSFRow row = sheet.getRow(rowIndex);
			if (row == null) {
				continue;
			}
			String values[] = getExcelRole(row);
			result.add(values);
		}
		return result;
	}

	// end 获取excle 一个表(sheet)的记录
	// start 获取excle 一行的记录
	public String[] getExcelRole(XSSFRow xSSFRow) {

		XSSFRow row = xSSFRow;
		int rowSize = 0;
		XSSFCell cell = null;

		if (row == null) {
			System.out.println("空空");
			return null;
		}
		int tempRowSize = row.getLastCellNum() + 1;// 获取列数
		if (tempRowSize > rowSize) {
			rowSize = tempRowSize;// 获取列数
		}
		String[] values = new String[rowSize];// values store a row 's all
		// column value;
		Arrays.fill(values, "");
		// start all column for a row
		for (short columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) {
			String value = "";
			cell = row.getCell(columnIndex);
			if (cell != null) {
				// 注意：一定要设成这个，否则可能会出现乱码
				// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				switch (cell.getCellType()) {
				case XSSFCell.CELL_TYPE_STRING:
					value = cell.getStringCellValue();
					break;
				case XSSFCell.CELL_TYPE_NUMERIC:
					if (HSSFDateUtil.isCellDateFormatted(cell)) {
						Date date = (Date) cell.getDateCellValue();
						if (date != null) {
							value = new SimpleDateFormat("yyyy-MM-dd").format(date);
						} else {
							value = "";
						}
					} else {
						value = new DecimalFormat("0").format(cell.getNumericCellValue());
					}
					break;
				case XSSFCell.CELL_TYPE_FORMULA:
					// 导入时如果为公式生成的数据则无值
					if (!cell.getStringCellValue().equals("")) {
						value = cell.getStringCellValue();
					} else {
						value = cell.getNumericCellValue() + "";
					}
					break;
				case XSSFCell.CELL_TYPE_BLANK:
					break;
				case XSSFCell.CELL_TYPE_ERROR:
					value = "";
					break;
				case XSSFCell.CELL_TYPE_BOOLEAN:
					value = (cell.getBooleanCellValue() == true ? "Y" : "N");
					break;
				default:
					value = "";
				}
			}
			//如果行开始第一个Cell格为空,则跳过此行
			if (columnIndex == 0 && value.trim().equals("")) {
				break;
			}
			values[columnIndex] = this.rightTrim(value);
		}
		return values;
	}

	// end 获取excle 一行的记录

	public String rightTrim(String str) {
		if (str == null) {
			return "";
		}

		int length = str.length();
		for (int i = length - 1; i >= 0; i--) {
			if (str.charAt(i) != 0x20) {
				break;
			}
			length--;
		}
		return str.substring(0, length);
	}
}